﻿/***************************************************
//  Copyright (c) Premium Tax Free 2011
***************************************************/

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using PremierTaxFree.PTFLib.Properties;
using PremierTaxFree.PTFLib.Extensions;
using System.Diagnostics;
using System.Data;
using System.Collections;
using System.Threading;
using System.Transactions;

namespace PremierTaxFree.PTFLib.Data
{
    public static class DataAccess
    {
        public static string ConnectionStringMaster_Client
        {
            get
            {
                return "workstation id=.;packet size=4096;integrated security=SSPI;persist security info=False;initial catalog=master";
            }
        }

        public static string ConnectionString_Client
        {
            get
            {
                return "workstation id=.;packet size=4096;integrated security=SSPI;persist security info=False;initial catalog=LocalDB";
            }
        }

        public static string ConnectionString_Server { get; set; }

        public static void CreateDb_Client()
        {
            const string sql = "CREATE DATABASE LocalDB;";
            SQL.ExecuteNonQuery(ConnectionStringMaster_Client, sql);
        }

        public static void CreateTables_Client()
        {
            SQL.ExecuteNonQuery(ConnectionString_Client, Resources.CreateDatabase_Client);
        }

        public static DataTable GetDatabases(bool onServer)
        {
            using (SqlConnection conn = new SqlConnection(onServer ? ConnectionString_Server : ConnectionString_Client))
            {
                conn.Open();
                DataTable databases = conn.GetSchema("Databases");
                return databases;
            }
        }

        #region INSERT FILE

        public static int InsertFile_Client(string name, string content)
        {
            return SQL.ExecuteNonQuery(CreateInsertFileCommand_Client(name, content), true);
        }

        public static void InsertFileAsync_Client(string name, string content)
        {
            SQLWorker.Default.Add(CreateInsertFileCommand_Client(name, content), true);
        }

        public static SqlCommand CreateInsertFileCommand_Client(string name, string content)
        {
            const string sql = "INSERT INTO FILES(Name, Content, DateInserted) VALUES (@Name, @Content, @Date);";
            SqlCommand command = SQL.CreateCommand(ConnectionString_Client, sql,
                        new SqlParameter("@Name", name),
                        new SqlParameter("@Content", content),
                        new SqlParameter("@Date", DateTime.Now));
            return command;
        }

        public static int InsertFile_Server(
            string name, string clientIP, string countryCode, string retailerID, string voucherID,
            byte[] voucherImage, byte[] barCodeImage)
        {
            return SQL.ExecuteNonQuery(
                CreateInsertFileCommand_Server(name, clientIP, countryCode,
                retailerID, voucherID, voucherImage, barCodeImage), true);
        }

        public static void InsertFileAsync_Server(
            string name, string clientIP, string countryCode, string retailerID, string voucherID,
            byte[] voucherImage, byte[] barCodeImage)
        {
            SQLWorker.Default.Add(
                CreateInsertFileCommand_Server(name, clientIP, countryCode,
                retailerID, voucherID, voucherImage, barCodeImage), true);
        }

        public static SqlCommand CreateInsertFileCommand_Server(
            string name, string clientIP, string countryCode, string retailerID, string voucherID, 
            byte[] voucherImage, byte[] barCodeImage)
        {
            Debug.Assert(!string.IsNullOrEmpty(ConnectionString_Server));

            const string sql = "INSERT INTO FILES( " +
                "Name, ClientIP, CountryCode, RetailerID, VoucherID, VoucherImage, BarCodeImage, DateInserted) VALUES(" +
                "@Name, @ClientIP, @CountryCode, @RetailerID, @VoucherID, @VoucherImage, @BarCodeImage, @Date);";
            SqlCommand command = SQL.CreateCommand(ConnectionString_Server, sql,
                        new SqlParameter("@Name", name),
                        new SqlParameter("@ClientIP", clientIP),
                        new SqlParameter("@CountryCode", countryCode),
                        new SqlParameter("@RetailerID", retailerID),
                        new SqlParameter("@VoucherID", voucherID),
                        new SqlParameter("@VoucherImage", voucherImage),
                        new SqlParameter("@BarCodeImage", barCodeImage),
                        new SqlParameter("@Date", DateTime.Now));
            return command;
        }

        #endregion

        public static List<DbFileInfo> SelectFilesForExport_Client()
        {
            const string sql = "SELECT FileID, Name, Content, DateInserted FROM FILES WHERE DateExported IS NULL;";
            var list = SQL.ExecuteReader<DbFileInfo>(ConnectionString_Client, sql);
            return list;
        }

        #region SET FILES SENT

        public static int SetFilesSent_Client(List<DbFileInfo> files)
        {
            if (files.Count == 0)
                return 0;

            return SQL.ExecuteNonQuery(CreateSetFilesSentCommand_Client(files), true);
        }

        public static void SetFilesSentAsync_Client(List<DbFileInfo> files)
        {
            if (files.Count == 0)
                return;

            SQLWorker.Default.Add(CreateSetFilesSentCommand_Client(files), true);
        }

        public static SqlCommand CreateSetFilesSentCommand_Client(List<DbFileInfo> files)
        {
            const string sql = "UPDATE FILES Set DateExported = @Date WHERE FileID in (";
            StringBuilder bsql = new StringBuilder(sql);
            files.ForEach((file) => { bsql.AppendFormat("{0},", file.ID); });
            bsql.ReplaceLast(',', ')');
            return SQL.CreateCommand(ConnectionString_Client,
                    bsql.ToString(),
                    new SqlParameter("@Date", DateTime.Now));
        }

        #endregion

        #region DELETE SEND FILES

        public static int DeleteSentFiles_Client()
        {
            return SQL.ExecuteNonQuery(CreateDeleteSentFilesCommand_Client(), true);
        }

        public static void DeleteSentFilesAsync_Client()
        {
            SQLWorker.Default.Add(CreateDeleteSentFilesCommand_Client(), true);
        }

        private static SqlCommand CreateDeleteSentFilesCommand_Client()
        {
            const string sql = "DELETE FILES WHERE DateInserted IS NOT NULL;";
            return SQL.CreateCommand(ConnectionString_Client, sql);
        }

        #endregion

        #region DELETE OLDER SENT FILES

        public static int DeleteOlderSentFiles_Client()
        {
            return SQL.ExecuteNonQuery(CreateDeleteOlderSentFiles_Client(), true);
        }

        public static void DeleteOlderSentFilesAsync_Client()
        {
            SQLWorker.Default.Add(CreateDeleteOlderSentFiles_Client(), true);
        }

        private static SqlCommand CreateDeleteOlderSentFiles_Client()
        {
            const int WEEK = 7;
            const string sql = "DELETE FILES WHERE DateInserted < @Date;";
            return SQL.CreateCommand(ConnectionString_Client,
                    sql,
                    new SqlParameter("@Date", DateTime.Now.AddDays(-WEEK)));
        }

        #endregion

        #region INSERT MESSAGE

        /// <summary>
        /// Insert message to the database. Do not fire errors.
        /// </summary>
        /// <param name="message"></param>
        /// <param name="source"></param>
        /// <param name="stacktrase"></param>
        /// <param name="onServer"></param>
        public static void InsertMessage(string message, string source, eMessageTypes type, string stacktrase, bool onServer)
        {
            // Do not refire errors
            SQL.ExecuteNonQuery(CreateInsertMessage(message, source, type, stacktrase, onServer), false);
        }

        /// <summary>
        /// Insert message to the database. Do not fire errors.
        /// </summary>
        /// <param name="message"></param>
        /// <param name="source"></param>
        /// <param name="stacktrase"></param>
        /// <param name="onServer"></param>
        public static void InsertMessageAsync(string message, string source, eMessageTypes type, string stacktrase, bool onServer)
        {
            // Do not refire errors
            SQLWorker.Default.Add(CreateInsertMessage(message, source, type, stacktrase, onServer), false);
        }

        /// <summary>
        /// Message database is one and the same for both server and client
        /// </summary>
        /// <param name="message"></param>
        /// <param name="source"></param>
        /// <param name="stacktrase"></param>
        /// <param name="onServer"></param>
        /// <returns></returns>
        private static SqlCommand CreateInsertMessage(string message, string source, eMessageTypes type, string stacktrase, bool onServer)
        {
            const string sql = "INSERT INTO MESSAGES(Message, Source, Type, StackTrace, DateInserted) VALUES " +
                                        "(@Message, @Source, @Type, @StackTrace, @DateInserted);";
            return SQL.CreateCommand(onServer ? ConnectionString_Server : ConnectionString_Client,
                sql,
                new SqlParameter("@Message", message),
                new SqlParameter("@Source", source),
                new SqlParameter("@Type", Convert.ToInt32(type)),
                new SqlParameter("@StackTrace", stacktrase),
                new SqlParameter("@DateInserted", DateTime.Now));
        }

        #endregion

        #region CONFIG

        public static IList<DbConfigInfo> SelectConfig(IDictionary dict)
        {
            const string sql = "SELECT Name, Value FROM CONFIG;";
            IList<DbConfigInfo> list = SQL.ExecuteReader<DbConfigInfo>(ConnectionString_Server, sql);
            return list;
        }

        public static void UpdateConfig(IDictionary dict)
        {
            Thread.BeginCriticalRegion();
            try
            {
                DataSchemaServer.CONFIGDataTable config = new DataSchemaServer.CONFIGDataTable();
                foreach (var key in dict.Keys)
                {
                    config.AddCONFIGRow(Convert.ToString(key), dict[key]);
                }

                using (TransactionScope scope = new TransactionScope())
                {
                    SQL.ExecuteNonQuery(ConnectionString_Server, "TRUNCATE TABLE CONFIG;");
                    SQL.ExecuteTable(ConnectionString_Server, "CONFIG", config);

                    scope.Complete();
                }
            }
            finally
            {
                Thread.EndCriticalRegion();
            }
        }

        #endregion

        #region RESET DATABASE

        public static void ResetDatabase_Client()
        {
            SQL.ExecuteNonQuery(ConnectionString_Client, Resources.ResetDatabase_Client);
        }

        public static void ResetDatabase_Server()
        {
            SQL.ExecuteNonQuery(ConnectionString_Client, Resources.ResetDatabase_Server);
        }

        #endregion
    }
}
